﻿using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using Common.Constants;
using Common.Helper;
using Entities;


namespace DataAccess
{
    public class ServiceDataAccess
    {
        /// <summary>
        /// Gets the instance.
        /// </summary>
        /// <value>
        /// The instance.
        /// </value>
        public static ServiceDataAccess Instance { get; private set; }

        /// <summary>
        /// Initializes the <see cref="AddressDataAccess" /> class.
        /// </summary>
        static ServiceDataAccess() { Instance = new ServiceDataAccess(); }

        /// <summary>
        /// Extracts the raw data.
        /// </summary>
        /// <param name="dataReader">The data reader.</param>
        /// <returns></returns>
        private static Service ExtractRawData(IDataRecord dataReader)
        {
            var service = new Service();
            service.Accreditations = dataReader["Accreditations"].ToString();
            service.BarriersCriterion = dataReader["BarriersCriterion"].ToString();
            service.BenefitsCriterion = dataReader["BenefitsCriterion"].ToString();
            service.ClientDescription = dataReader["ClientDescription"].ToString();
            service.ClientJourney = dataReader["ClientJourney"].ToString();
            service.ClientOutcome = dataReader["ClientOutcome"].ToString();
            service.ClientSupportProcess = dataReader["ClientSupportProcess"].ToString();
            if (!string.IsNullOrEmpty(dataReader["ContactId"].ToString()))
                service.ContactId = (Guid)dataReader["ContactId"];
            service.ContractCode = dataReader["ContractCode"].ToString();
            service.ContractObligation = dataReader["ContractObligation"].ToString();
            service.ContractOutcome = dataReader["ContractOutcome"].ToString();
            service.ContractValue = dataReader["ContractValue"].ToString();
            service.DeptCode = dataReader["DeptCode"].ToString();
            service.DescriptionDelivery = dataReader["DescriptionDelivery"].ToString();
            service.Disability = dataReader["Disability"].ToString();
            DateTime endDate;
            if (DateTime.TryParse(dataReader["EndDate"].ToString(), out endDate))
                service.EndDate = endDate.ToString();
            service.EthnicityCriterion = dataReader["EthnicityCriterion"].ToString();
            service.Extendable = dataReader["Extendable"].ToString();
            service.FullDescription = dataReader["FullDescription"].ToString();
            decimal fundingAmount;
            if (decimal.TryParse(dataReader["BarriersCriterion"].ToString(), out fundingAmount))
                service.FundingAmount = fundingAmount;
            if (!string.IsNullOrEmpty(dataReader["FundingContactId"].ToString()))
                service.FundingContactId = (Guid)dataReader["FundingContactId"];

            service.FundingContinuationDetails = dataReader["FundingContinuationDetails"].ToString();
            if (!string.IsNullOrEmpty(dataReader["FundingContinuationNeeded"].ToString()))
                service.FundingContinuationNeeded = (bool)dataReader["FundingContinuationNeeded"];
            decimal fundingCoutinuationAmount;
            if (decimal.TryParse(dataReader["FundingCoutinuationAmount"].ToString(), out fundingCoutinuationAmount))
                service.FundingCoutinuationAmount = fundingCoutinuationAmount;
            DateTime fundingEnd;
            if (DateTime.TryParse(dataReader["FundingEnd"].ToString(), out fundingEnd))
                service.FundingEnd = fundingEnd.ToString();
            decimal fundingNeeds;
            if (decimal.TryParse(dataReader["FundingNeeds"].ToString(), out fundingNeeds))
                service.FundingNeeds = fundingNeeds;

            service.FundingSource = dataReader["FundingSource"].ToString();
            DateTime fundingStart;
            if (DateTime.TryParse(dataReader["BarriersCriterion"].ToString(), out fundingStart))
                service.FundingStart = fundingStart.ToString();
            bool fundraisingComplete;
            if (bool.TryParse(dataReader["FundraisingComplete"].ToString(), out fundraisingComplete))
                service.FundraisingComplete = fundraisingComplete;

            DateTime fundraisingCompleteDate;
            if (DateTime.TryParse(dataReader["FundraisingCompleteDate"].ToString(), out fundraisingCompleteDate))
                service.FundraisingCompleteDate = fundraisingCompleteDate.ToString();
            bool fundraisingDonationIncremental;
            if (bool.TryParse(dataReader["FundraisingDonationIncremental"].ToString(), out fundraisingDonationIncremental))
                service.FundraisingDonationIncremental = fundraisingDonationIncremental;
            DateTime fundraisingDonationDate;
            if (DateTime.TryParse(dataReader["FundraisingDonationDate"].ToString(), out fundraisingDonationDate))
                service.FundraisingDonationDate = fundraisingDonationDate.ToString();
            decimal fundraisingDonorAmount;
            if (decimal.TryParse(dataReader["FundraisingDonorAmount"].ToString(), out fundraisingDonorAmount))
                service.FundraisingDonorAmount = fundraisingDonorAmount;
            service.FundraisingForText = dataReader["FundraisingForText"].ToString();
            decimal fundraisingNeeds;
            if (decimal.TryParse(dataReader["FundraisingNeeds"].ToString(), out fundraisingNeeds))
                service.FundraisingNeeds = fundraisingNeeds;
            DateTime fundraisingRequiredBy;
            if (DateTime.TryParse(dataReader["FundraisingRequiredBy"].ToString(), out fundraisingRequiredBy))
                service.FundraisingRequiredBy = fundraisingRequiredBy.ToString();

            service.FundraisingWhy = dataReader["FundraisingWhy"].ToString();

            service.InterventionId = dataReader["InterventionId"].ToString();

            if (!string.IsNullOrEmpty(dataReader["OtherServiceId"].ToString()))
                service.OtherServiceId = dataReader["OtherServiceId"].ToString();
            service.OtherServiceParticiPationCriterion = dataReader["OtherServiceParticiPationCriterion"].ToString();
            service.Participation = dataReader["Participation"].ToString();
            service.ProcessMethod = dataReader["ProcessMethod"].ToString();
            if (!string.IsNullOrEmpty(dataReader["ProgrammeId"].ToString()))
                service.ProgrammeId = (Guid)dataReader["ProgrammeId"];
            service.ReferralSources = dataReader["ReferralSources"].ToString();
            bool serviceActive;
            if (bool.TryParse(dataReader["ServiceActive"].ToString(), out serviceActive))
                service.ServiceActive = serviceActive;
            service.ServiceAttendance = dataReader["ServiceAttendance"].ToString();
            if (dataReader["ServiceId"] != null)
                service.ServiceId = (Guid)dataReader["ServiceId"];
            service.ServiceName =dataReader["ServiceName"].ToString();
            int serviceType;
            if (int.TryParse(dataReader["ServiceType"].ToString(), out serviceType))
                service.ServiceType = serviceType;
            service.ShortDescription = dataReader["ShortDescription"].ToString();
            bool stagedPayment;
            if (bool.TryParse(dataReader["StagedPayment"].ToString(), out stagedPayment))
                service.StagedPayment = stagedPayment;
            DateTime startDate;
            if (DateTime.TryParse(dataReader["StartDate"].ToString(), out startDate))
                service.StartDate = startDate.ToString();
            DateTime startExpected;
            if (DateTime.TryParse(dataReader["StartExpected"].ToString(), out startExpected))
                service.StartExpected = startExpected.ToString();
            int subType;
            if (int.TryParse(dataReader["SubType"].ToString(), out subType))
                service.SubType = subType;
            service.SupportCentres = dataReader["SupportCentres"].ToString();
            service.TargetClient = dataReader["TargetClient"].ToString();
            service.TimeLimited = dataReader["TimeLimited"].ToString();

            return service;
        }

        /// <summary>
        /// Lists the service.
        /// </summary>
        /// <param name="includeInactive">if set to <c>true</c> [include inactive].</param>
        /// <returns></returns>
        public Collection<Service> ListService(bool includeInactive)
        {
            var listService = new Collection<Service>();
            var collParameter = new Collection<SqlParameter>
                                    {
                                        new SqlParameter("includeInactive", includeInactive)
                                    };
            using (var reader = SqlHelper.ExecuteReader(ProcedureStores.GetAllService, collParameter))
            {
                while (reader.Read())
                {
                    var result = ExtractRawData(reader);
                    listService.Add(result);
                }
                return listService;
            }
        }

        /// <summary>
        /// Inserts the service.
        /// </summary>
        /// <param name="service">The service.</param>
        /// <returns></returns>
        public int InsertService(Service service)
        {
            if (service == null)
            {
                return -1;
            }
            if (String.IsNullOrEmpty(service.ServiceName) || String.IsNullOrEmpty(service.ShortDescription) || String.IsNullOrEmpty(service.DeptCode) || service.ServiceType == 0)
            {
                return -1;
            }
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("ServiceId",service.ServiceId),
                                       new SqlParameter("ContactId",service.ContactId),
                                       new SqlParameter("ServiceName",service.ServiceName),
                                       new SqlParameter("ShortDescription",service.ShortDescription),
                                       new SqlParameter("SubType",service.SubType),
                                       new SqlParameter("ClientDescription",service.ClientDescription),
                                       new SqlParameter("ServiceAttendance",service.ServiceAttendance),
                                       new SqlParameter("StartExpected",service.StartExpected),
                                       new SqlParameter("StartDate",service.StartDate),
                                       new SqlParameter("EndDate",service.EndDate),
                                       new SqlParameter("Extendable",service.Extendable),
                                       new SqlParameter("ServiceActive",service.ServiceActive),
                                       new SqlParameter("FullDescription",service.FullDescription),
                                       new SqlParameter("DeptCode",service.DeptCode),
                                       new SqlParameter("ServiceType",service.ServiceType),
                                       new SqlParameter("DescriptionDelivery",service.DescriptionDelivery),
                                       new SqlParameter("StagedPayment",service.StagedPayment),
                                       new SqlParameter("ProcessMethod",service.ProcessMethod),
                                       new SqlParameter("TimeLimited",service.TimeLimited),
                                       new SqlParameter("BenefitsCriterion",service.BenefitsCriterion),
                                       new SqlParameter("BarriersCriterion",service.BarriersCriterion),
                                       new SqlParameter("EthnicityCriterion",service.EthnicityCriterion),
                                       new SqlParameter("Disability",service.Disability),
                                       new SqlParameter("PersonalCircumstances",service.PersonalCircumstances),
                                       new SqlParameter("OtherServiceParticiPationCriterion",service.OtherServiceParticiPationCriterion),
                                       new SqlParameter("ClientSupportProcess",service.ClientSupportProcess),
                                       new SqlParameter("ClientOutcome",service.ClientOutcome),
                                       new SqlParameter("TargetClient",service.TargetClient),
                                       new SqlParameter("ReferralSources",service.ReferralSources),
                                       new SqlParameter("SupportCentres",service.SupportCentres),
                                       new SqlParameter("OtherServiceId",service.OtherServiceId),
                                       new SqlParameter("ProgrammeId",service.ProgrammeId),
                                       new SqlParameter("ContractOutcome",service.ContractOutcome),
                                       new SqlParameter("ContractObligation",service.ContractObligation),
                                       new SqlParameter("Participation",service.Participation),
                                       new SqlParameter("ContractCode",service.ContractCode),
                                       new SqlParameter("ContractValue",service.ContractValue),
                                       new SqlParameter("FundingSource",service.FundingSource),
                                       new SqlParameter("FundingContactId",service.FundingContactId),
                                       new SqlParameter("FundingAmount",service.FundingAmount),
                                       new SqlParameter("FundingStart",service.FundingStart),
                                       new SqlParameter("FundingEnd",service.FundingEnd),
                                       new SqlParameter("FundingNeeds",service.FundingNeeds),
                                       new SqlParameter("FundingContinuationNeeded",service.FundingContinuationNeeded),
                                       new SqlParameter("FundingCoutinuationAmount",service.FundingCoutinuationAmount),
                                       new SqlParameter("FundingContinuationDetails",service.FundingContinuationDetails),
                                       new SqlParameter("FundraisingForText",service.FundraisingForText),
                                       new SqlParameter("FundraisingWhy",service.FundraisingWhy),
                                       new SqlParameter("FundraisingNeeds",service.FundraisingNeeds),
                                       new SqlParameter("FundraisingRequiredBy",service.FundraisingRequiredBy),
                                       new SqlParameter("FundraisingComplete",service.FundraisingComplete),
                                       new SqlParameter("FundraisingCompleteDate",service.FundraisingCompleteDate),
                                       new SqlParameter("FundraisingDonorAnonymous",service.FundraisingDonorAnonym),
                                       new SqlParameter("FundraisingDonorAmount",service.FundraisingDonorAmount),
                                       new SqlParameter("FundraisingDonationDate",service.FundraisingDonationDate),
                                       new SqlParameter("FundraisingDonationIncremental",service.FundraisingDonationIncremental),
                                       new SqlParameter("ClientJoumey",service.ClientJourney),
                                       new SqlParameter("Accreditations",service.Accreditations),
                                       new SqlParameter("InterventionId",service.InterventionId),
                                   };
            return SqlHelper.ExecuteNonQuery(ProcedureStores.InsertService, colParameter);
        }

        /// <summary>
        /// Updates the service.
        /// </summary>
        /// <param name="service">The service.</param>
        /// <returns></returns>
        public int UpdateService(Service service)
        {
            if (service == null)
            {
                return -1;
            }
            if (String.IsNullOrEmpty(service.ServiceName) || String.IsNullOrEmpty(service.ShortDescription) || String.IsNullOrEmpty(service.DeptCode) || service.ServiceType == 0)
            {
                return -1;
            }
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("ServiceId",service.ServiceId),
                                       new SqlParameter("ContactId",service.ContactId),
                                       new SqlParameter("ServiceName",service.ServiceName),
                                       new SqlParameter("ShortDescription",service.ShortDescription),
                                       new SqlParameter("SubType",service.SubType),
                                       new SqlParameter("ClientDescription",service.ClientDescription),
                                       new SqlParameter("ServiceAttendance",service.ServiceAttendance),
                                       new SqlParameter("StartExpected",service.StartExpected),
                                       new SqlParameter("StartDate",service.StartDate),
                                       new SqlParameter("EndDate",service.EndDate),
                                       new SqlParameter("Extendable",service.Extendable),
                                       new SqlParameter("ServiceActive",service.ServiceActive),
                                       new SqlParameter("FullDescription",service.FullDescription),
                                       new SqlParameter("DeptCode",service.DeptCode),
                                       new SqlParameter("ServiceType",service.ServiceType),
                                       new SqlParameter("DescriptionDelivery",service.DescriptionDelivery),
                                       new SqlParameter("StagedPayment",service.StagedPayment),
                                       new SqlParameter("ProcessMethod",service.ProcessMethod),
                                       new SqlParameter("TimeLimited",service.TimeLimited),
                                       new SqlParameter("BenefitsCriterion",service.BenefitsCriterion),
                                       new SqlParameter("BarriersCriterion",service.BarriersCriterion),
                                       new SqlParameter("EthnicityCriterion",service.EthnicityCriterion),
                                       new SqlParameter("Disability",service.Disability),
                                       new SqlParameter("PersonalCircumstances",service.PersonalCircumstances),
                                       new SqlParameter("OtherServiceParticiPationCriterion",service.OtherServiceParticiPationCriterion),
                                       new SqlParameter("ClientSupportProcess",service.ClientSupportProcess),
                                       new SqlParameter("ClientOutcome",service.ClientOutcome),
                                       new SqlParameter("TargetClient",service.TargetClient),
                                       new SqlParameter("ReferralSources",service.ReferralSources),
                                       new SqlParameter("SupportCentres",service.SupportCentres),
                                       new SqlParameter("OtherServiceId",service.OtherServiceId),
                                       new SqlParameter("ProgrammeId",service.ProgrammeId),
                                       new SqlParameter("ContractOutcome",service.ContractOutcome),
                                       new SqlParameter("ContractObligation",service.ContractObligation),
                                       new SqlParameter("Participation",service.Participation),
                                       new SqlParameter("ContractCode",service.ContractCode),
                                       new SqlParameter("ContractValue",service.ContractValue),
                                       new SqlParameter("FundingSource",service.FundingSource),
                                       new SqlParameter("FundingContactId",service.FundingContactId),
                                       new SqlParameter("FundingAmount",service.FundingAmount),
                                       new SqlParameter("FundingStart",service.FundingStart),
                                       new SqlParameter("FundingEnd",service.FundingEnd),
                                       new SqlParameter("FundingNeeds",service.FundingNeeds),
                                       new SqlParameter("FundingContinuationNeeded",service.FundingContinuationNeeded),
                                       new SqlParameter("FundingCoutinuationAmount",service.FundingCoutinuationAmount),
                                       new SqlParameter("FundingContinuationDetails",service.FundingContinuationDetails),
                                       new SqlParameter("FundraisingForText",service.FundraisingForText),
                                       new SqlParameter("FundraisingWhy",service.FundraisingWhy),
                                       new SqlParameter("FundraisingNeeds",service.FundraisingNeeds),
                                       new SqlParameter("FundraisingRequiredBy",service.FundraisingRequiredBy),
                                       new SqlParameter("FundraisingComplete",service.FundraisingComplete),
                                       new SqlParameter("FundraisingCompleteDate",service.FundraisingCompleteDate),
                                       new SqlParameter("FundraisingDonorAnonym",service.FundraisingDonorAnonym),
                                       new SqlParameter("FundraisingDonorAmount",service.FundraisingDonorAmount),
                                       new SqlParameter("FundraisingDonationDate",service.FundraisingDonationDate),
                                       new SqlParameter("FundraisingDonationIncremental",service.FundraisingDonationIncremental),
                                       new SqlParameter("ClientJourney",service.ClientJourney),
                                       new SqlParameter("Accreditations",service.Accreditations),
                                       new SqlParameter("InterventionId",service.InterventionId),
                                   };
            return SqlHelper.ExecuteNonQuery(ProcedureStores.UpdateService, colParameter);
        }

        /// <summary>
        /// Gets the by id.
        /// </summary>
        /// <param name="serviceId">The service id.</param>
        /// <returns></returns>
        public Service GetById(Guid serviceId)
        {
            if (serviceId == Guid.Empty)
            {
                return null;
            }
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("ServiceId", serviceId)
                                   };
            var dataReader = SqlHelper.ExecuteReader(ProcedureStores.GetServiceById, colParameter);
            if (dataReader.Read())
                return ExtractRawData(dataReader);
            return null;
        }


        /// <summary>
        /// Gets the name of the different.
        /// </summary>
        /// <param name="serviceId">The service id.</param>
        /// <param name="name">The name.</param>
        /// <returns></returns>
        public IDataReader GetDifferentName(Guid serviceId, string name)
        {
            if (String.IsNullOrEmpty(name))
            {
                return null;
            }
            var colParamter = new Collection<SqlParameter>
                                  {
                                      new SqlParameter("ServiceId", serviceId),
                                      new SqlParameter("ServiceName", name)
                                  };
            var dataReader = SqlHelper.ExecuteReader(ProcedureStores.GetDifferentServiceName, colParamter);
            return dataReader;


        }

        //public Collection<Service> ListService(bool includeInactive)
        //{
        //    var listService = new Collection<Service>();
        //    var collParameter = new Collection<SqlParameter>
        //                            {
        //                                new SqlParameter("includeInactive", includeInactive)
        //                            };
        //    using (var reader = SqlHelper.ExecuteReader(ProcedureStores.GetAllService, collParameter))
        //    {
        //        while (reader.Read())
        //        {
        //            var result = ExtractRawData(reader);
        //            listService.Add(result);
        //        }
        //        return listService;
        //    }
        //}

        /// <summary>
        /// Changes the status.
        /// </summary>
        /// <param name="serviceId">The service id.</param>
        /// <param name="isactive">if set to <c>true</c> [isactive].</param>
        /// <returns></returns>
        public int ChangeStatus(Guid serviceId, bool isactive)
        {
            if (serviceId == Guid.Empty)
            {
                return -1;
            }
            var colParameter = new Collection<SqlParameter>
                                   {
                                       new SqlParameter("ServiceID",serviceId),
                                       new SqlParameter("ServiceActive",isactive)
                                   };
            var result = SqlHelper.ExecuteNonQuery(ProcedureStores.ChangeStatusService, colParameter);
            return result;
        }
    }
}
